package com.hjy.cabinet.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.hjy.cabinet.db.domain.OperationLog;

import java.util.ArrayList;
import java.util.List;

public class OperationLogDBHelper extends SQLiteOpenHelper {
    private static final String DATABASE_NAME = "OperationLog.db";
    private static final int DATABASE_VERSION = 30; // 更新版本号

    private static final String TABLE_NAME = "operation_log";
    private static final String COLUMN_ID = "id";
    private static final String COLUMN_USER_ID = "user_id";
    private static final String COLUMN_USERNAME = "username";
    private static final String COLUMN_EVENT = "event";
    private static final String COLUMN_OPERATION_TYPE = "operation_type";
    private static final String COLUMN_OPERATION_TIME = "operation_time";
    private static final String COLUMN_OPERATION_STATUS = "operation_status"; // 新字段

    private static final String CREATE_TABLE = "CREATE TABLE " + TABLE_NAME + " (" +
            COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
            COLUMN_USER_ID + " INTEGER, " +
            COLUMN_USERNAME + " TEXT, " +
            COLUMN_EVENT + " TEXT, " +
            COLUMN_OPERATION_TYPE + " TEXT, " +
            COLUMN_OPERATION_TIME + " TEXT, " +
            COLUMN_OPERATION_STATUS + " INTEGER" + // 将Boolean存储为INTEGER
            ");";

    public OperationLogDBHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CREATE_TABLE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
        onCreate(db);
    }


    // 删除操作日志
    public int deleteOperationLog(int id) {
        SQLiteDatabase db = this.getWritableDatabase();
        int result = db.delete(TABLE_NAME, COLUMN_ID + "=?", new String[]{String.valueOf(id)});
        db.close();
        return result;
    }

    // 添加操作日志
    public long addOperationLog(int userId, String username, String event, String operationType, String operationTime, Boolean operationStatus) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(COLUMN_USER_ID, userId);
        values.put(COLUMN_USERNAME, username);
        values.put(COLUMN_EVENT, event);
        values.put(COLUMN_OPERATION_TYPE, operationType);
        values.put(COLUMN_OPERATION_TIME, operationTime);
        values.put(COLUMN_OPERATION_STATUS, operationStatus ? 1 : 0); // 将Boolean转换为INTEGER
        long id = db.insert(TABLE_NAME, null, values);
        db.close();
        return id;
    }

    // 更新操作日志
    public int updateOperationLog(int id, int userId, String username, String event, String operationType, String operationTime, Boolean operationStatus) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(COLUMN_USER_ID, userId);
        values.put(COLUMN_USERNAME, username);
        values.put(COLUMN_EVENT, event);
        values.put(COLUMN_OPERATION_TYPE, operationType);
        values.put(COLUMN_OPERATION_TIME, operationTime);
        values.put(COLUMN_OPERATION_STATUS, operationStatus ? 1 : 0); // 将Boolean转换为INTEGER
        int result = db.update(TABLE_NAME, values, COLUMN_ID + "=?", new String[]{String.valueOf(id)});
        db.close();
        return result;
    }

    // 查询所有操作日志，返回实体类列表
    public List<OperationLog> getAllOperationLogs() {
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery("SELECT * FROM " + TABLE_NAME, null);
        List<OperationLog> operationLogs = new ArrayList<>();
        if (cursor.moveToFirst()) {
            int idIndex = cursor.getColumnIndexOrThrow(COLUMN_ID);
            int userIdIndex = cursor.getColumnIndexOrThrow(COLUMN_USER_ID);
            int usernameIndex = cursor.getColumnIndexOrThrow(COLUMN_USERNAME);
            int eventIndex = cursor.getColumnIndexOrThrow(COLUMN_EVENT);
            int operationTypeIndex = cursor.getColumnIndexOrThrow(COLUMN_OPERATION_TYPE);
            int operationTimeIndex = cursor.getColumnIndexOrThrow(COLUMN_OPERATION_TIME);
            int operationStatusIndex = cursor.getColumnIndexOrThrow(COLUMN_OPERATION_STATUS); // 新字段索引

            do {
                int id = cursor.getInt(idIndex);
                int userId = cursor.getInt(userIdIndex);
                String username = cursor.getString(usernameIndex);
                String event = cursor.getString(eventIndex);
                String operationType = cursor.getString(operationTypeIndex);
                String operationTime = cursor.getString(operationTimeIndex);
                Boolean operationStatus = cursor.getInt(operationStatusIndex) == 1; // 读取并转换为Boolean
                OperationLog operationLog = new OperationLog(id, userId, username, event, operationType, operationTime, operationStatus); // 创建对象时包含新字段
                operationLogs.add(operationLog);
            } while (cursor.moveToNext());
        }
        cursor.close();
        db.close();
        return operationLogs;
    }

    // 根据id查询操作日志，返回实体类实例
    public OperationLog getOperationLogById(int id) {
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery("SELECT * FROM " + TABLE_NAME + " WHERE " + COLUMN_ID + "=?", new String[]{String.valueOf(id)});
        OperationLog operationLog = null;
        if (cursor.moveToFirst()) {
            int idIndex = cursor.getColumnIndexOrThrow(COLUMN_ID);
            int userIdIndex = cursor.getColumnIndexOrThrow(COLUMN_USER_ID);
            int usernameIndex = cursor.getColumnIndexOrThrow(COLUMN_USERNAME);
            int eventIndex = cursor.getColumnIndexOrThrow(COLUMN_EVENT);
            int operationTypeIndex = cursor.getColumnIndexOrThrow(COLUMN_OPERATION_TYPE);
            int operationTimeIndex = cursor.getColumnIndexOrThrow(COLUMN_OPERATION_TIME);
            int operationStatusIndex = cursor.getColumnIndexOrThrow(COLUMN_OPERATION_STATUS); // 新字段索引

            int logId = cursor.getInt(idIndex);
            int userId = cursor.getInt(userIdIndex);
            String username = cursor.getString(usernameIndex);
            String event = cursor.getString(eventIndex);
            String operationType = cursor.getString(operationTypeIndex);
            String operationTime = cursor.getString(operationTimeIndex);
            Boolean operationStatus = cursor.getInt(operationStatusIndex) == 1; // 读取并转换为Boolean
            operationLog = new OperationLog(logId, userId, username, event, operationType, operationTime, operationStatus); // 创建对象时包含新字段
        }
        cursor.close();
        db.close();
        return operationLog;
    }

    // 根据用户名和时间段查询操作日志，支持分页和时间倒序
    public List<OperationLog> getOperationLogs(String username, String startDate, String endDate, int offset, int limit) {
        SQLiteDatabase db = this.getReadableDatabase();
        List<OperationLog> operationLogs = new ArrayList<>();

        // 基础查询
        StringBuilder queryBuilder = new StringBuilder("SELECT * FROM " + TABLE_NAME + " WHERE 1=1");

        // 添加条件
        List<String> selectionArgs = new ArrayList<>();

        if (username != null && !username.isEmpty()) {
            queryBuilder.append(" AND " + COLUMN_USERNAME + " LIKE ?");
            selectionArgs.add("%" + username + "%");
        }

        if (startDate != null && !startDate.isEmpty() && endDate != null && !endDate.isEmpty()) {
            queryBuilder.append(" AND " + COLUMN_OPERATION_TIME + " BETWEEN ? AND ?");
            selectionArgs.add(startDate);
            selectionArgs.add(endDate);
        }

        queryBuilder.append(" ORDER BY " + COLUMN_OPERATION_TIME + " DESC LIMIT ? OFFSET ?");
        selectionArgs.add(String.valueOf(limit));
        selectionArgs.add(String.valueOf(offset));

        // 执行查询
        Cursor cursor = db.rawQuery(queryBuilder.toString(), selectionArgs.toArray(new String[0]));

        if (cursor.moveToFirst()) {
            int idIndex = cursor.getColumnIndexOrThrow(COLUMN_ID);
            int userIdIndex = cursor.getColumnIndexOrThrow(COLUMN_USER_ID);
            int usernameIndex = cursor.getColumnIndexOrThrow(COLUMN_USERNAME);
            int eventIndex = cursor.getColumnIndexOrThrow(COLUMN_EVENT);
            int operationTypeIndex = cursor.getColumnIndexOrThrow(COLUMN_OPERATION_TYPE);
            int operationTimeIndex = cursor.getColumnIndexOrThrow(COLUMN_OPERATION_TIME);
            int operationStatusIndex = cursor.getColumnIndexOrThrow(COLUMN_OPERATION_STATUS); // 新字段索引

            do {
                int id = cursor.getInt(idIndex);
                int userId = cursor.getInt(userIdIndex);
                String userName = cursor.getString(usernameIndex);
                String event = cursor.getString(eventIndex);
                String operationType = cursor.getString(operationTypeIndex);
                String operationTime = cursor.getString(operationTimeIndex);
                Boolean operationStatus = cursor.getInt(operationStatusIndex) == 1; // 读取并转换为Boolean
                OperationLog operationLog = new OperationLog(id, userId, userName, event, operationType, operationTime, operationStatus); // 创建对象时包含新字段
                operationLogs.add(operationLog);
            } while (cursor.moveToNext());
        }

        cursor.close();
        db.close();
        return operationLogs;
    }

    // 根据 event = 0 查询操作日志
    public List<OperationLog> getOperationLogsByEvent() {
        SQLiteDatabase db = this.getReadableDatabase();
        List<OperationLog> operationLogs = new ArrayList<>();

        Cursor cursor = db.rawQuery("SELECT * FROM " + TABLE_NAME + " WHERE " + COLUMN_EVENT + " = ?", new String[]{"0"});

        if (cursor.moveToFirst()) {
            int idIndex = cursor.getColumnIndexOrThrow(COLUMN_ID);
            int userIdIndex = cursor.getColumnIndexOrThrow(COLUMN_USER_ID);
            int usernameIndex = cursor.getColumnIndexOrThrow(COLUMN_USERNAME);
            int eventIndex = cursor.getColumnIndexOrThrow(COLUMN_EVENT);
            int operationTypeIndex = cursor.getColumnIndexOrThrow(COLUMN_OPERATION_TYPE);
            int operationTimeIndex = cursor.getColumnIndexOrThrow(COLUMN_OPERATION_TIME);
            int operationStatusIndex = cursor.getColumnIndexOrThrow(COLUMN_OPERATION_STATUS); // 新字段索引

            do {
                int id = cursor.getInt(idIndex);
                int userId = cursor.getInt(userIdIndex);
                String username = cursor.getString(usernameIndex);
                String event = cursor.getString(eventIndex);
                String operationType = cursor.getString(operationTypeIndex);
                String operationTime = cursor.getString(operationTimeIndex);
                Boolean operationStatus = cursor.getInt(operationStatusIndex) == 1; // 读取并转换为Boolean
                OperationLog operationLog = new OperationLog(id, userId, username, event, operationType, operationTime, operationStatus); // 创建对象时包含新字段
                operationLogs.add(operationLog);
            } while (cursor.moveToNext());
        }

        cursor.close();
        db.close();
        return operationLogs;
    }
}